import sqlite3

DB_PATH = "machines.db"

# 初始化数据库并创建表
def init_db():
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    # 创建表，增加 blank_symbol 列
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS machines (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            raw_instructions TEXT,
            tape TEXT,
            state TEXT,
            head_position INTEGER,
            cycles INTEGER DEFAULT 0,
            blank_symbol TEXT DEFAULT ' '  -- 添加 blank_symbol 列
        )
    """)
    conn.commit()
    conn.close()

# 加载所有机器
def load_machines():
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM machines")
    rows = cursor.fetchall()
    
    # 统计字段总长度
    field_lengths = {
        "raw_instructions": 0,
        "tape": 0,
        "state": 0,
        "head_position": 0,
        "cycles": 0,
        "blank_symbol": 0
    }
    
    # 累加字段长度
    for row in rows:
        field_lengths["raw_instructions"] += len(row[1]) if row[1] else 0
        field_lengths["tape"] += len(row[2]) if row[2] else 0
        field_lengths["state"] += len(row[3]) if row[3] else 0
        field_lengths["head_position"] += len(str(row[4])) if row[4] is not None else 0
        field_lengths["cycles"] += len(str(row[5])) if row[5] is not None else 0
        field_lengths["blank_symbol"] += len(row[6]) if row[6] else 0
    
    # 计算总长度
    total_length = sum(field_lengths.values())
    
    # 计算比例
    field_ratios = {key: (value / total_length if total_length > 0 else 0) for key, value in field_lengths.items()}
    
    # 打印统计结果
    print("Field Lengths:", field_lengths)
    print("Total Length:", total_length)
    print("Field Ratios:", field_ratios)
    
    # 更新字典生成式以包含 blank_symbol 列
    machines = {
        str(row[0]): {
            "id": row[0],
            "raw_instructions": row[1],
            "tape": row[2],
            "state": row[3],
            "head_position": row[4],
            "cycles": row[5],
            "blank_symbol": row[6]  # 加载 blank_symbol 字段
        } for row in rows
    }
    conn.close()
    return machines
# 新增函数，排除 raw_instructions 字段
def load_machines_without_raw_instructions():
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM machines")
    rows = cursor.fetchall()

    # 统计字段总长度 (可以保留其他部分)
    field_lengths = {
        "tape": 0,
        "state": 0,
        "head_position": 0,
        "cycles": 0,
        "blank_symbol": 0
    }

    # 累加字段长度
    for row in rows:
        field_lengths["tape"] += len(row[2]) if row[2] else 0
        field_lengths["state"] += len(row[3]) if row[3] else 0
        field_lengths["head_position"] += len(str(row[4])) if row[4] is not None else 0
        field_lengths["cycles"] += len(str(row[5])) if row[5] is not None else 0
        field_lengths["blank_symbol"] += len(row[6]) if row[6] else 0

    # 计算总长度
    total_length = sum(field_lengths.values())

    # 计算比例
    field_ratios = {key: (value / total_length if total_length > 0 else 0) for key, value in field_lengths.items()}

    # 打印统计结果
    print("Field Lengths:", field_lengths)
    print("Total Length:", total_length)
    print("Field Ratios:", field_ratios)

    # 返回的字典不包含 raw_instructions 字段
    machines = {
        str(row[0]): {
            "id": row[0],
            "tape": row[2],
            "state": row[3],
            "head_position": row[4],
            "cycles": row[5],
            "blank_symbol": row[6]  # 加载 blank_symbol 字段
        } for row in rows
    }
    conn.close()
    return machines

# 根据ID加载机器
def load_machine_by_id(machine_id):
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM machines WHERE id = ?", (machine_id,))
    row = cursor.fetchone()
    conn.close()
    if row:
        return {
            "id": row[0],
            "raw_instructions": row[1],
            "tape": row[2],
            "state": row[3],
            "head_position": row[4],
            "cycles": row[5],
            "blank_symbol": row[6]  # 返回 blank_symbol 字段
        }
    return None

# 保存机器（插入新记录或更新现有记录）
def save_machine(machine_id=None, machine=None):
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    if machine_id is None:
        # 插入新机器，包含 blank_symbol 列
        cursor.execute("""
            INSERT INTO machines (raw_instructions, tape, state, head_position, cycles, blank_symbol)
            VALUES (?, ?, ?, ?, ?, ?)
        """, (
            machine["raw_instructions"],
            machine["tape"],
            machine["state"],
            machine["head_position"],
            machine.get("cycles", 0),
            machine.get("blank_symbol", ' ')  # 默认为空格
        ))
        machine_id = cursor.lastrowid  # 获取自增ID
    else:
        # 更新现有机器记录，包含 blank_symbol 列
        cursor.execute("""
            UPDATE machines
            SET raw_instructions = ?, tape = ?, state = ?, head_position = ?, cycles = ?, blank_symbol = ?
            WHERE id = ?
        """, (
            machine["raw_instructions"],
            machine["tape"],
            machine["state"],
            machine["head_position"],
            machine.get("cycles", 0),
            machine.get("blank_symbol", ' '),  # 默认为空格
            machine_id
        ))
    conn.commit()
    conn.close()
    return str(machine_id)

# 删除机器
def delete_machine(machine_id):
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    cursor.execute("DELETE FROM machines WHERE id = ?", (machine_id,))
    conn.commit()
    conn.close()
    return cursor.rowcount > 0  # 返回是否成功删除
